/*
 * $Id: DisciplinePlanningItemManager.java 8 2012-04-28 05:11:23Z forwardly@gmail.com $
 * 
 * Copyright (c) 2001-2008 Accentrix Company Limited. All Rights Reserved.
 * 
 * Accentrix Company Limited. ("Accentrix") retains copyright on all text, source
 * and binary code contained in this software and documentation. Accentrix grants
 * Licensee a limited license to use this software, provided that this copyright
 * notice and license appear on all copies of the software. The software source
 * code is provided for reference purposes only and may not be copied, modified 
 * or distributed.
 * 
 * THIS SOFTWARE AND DOCUMENTATION ARE PROVIDED "AS IS," WITHOUT ANY WARRANTY OF
 * ANY KIND UNLESS A SEPARATE WARRANTIES IS PURCHASED FROM ACCENTRIX AND REMAINS
 * VALID.  ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES,
 * INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
 * PURPOSE OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. ACCENTRIX SHALL NOT BE LIABLE
 * FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING OR MODIFYING THE
 * SOFTWARE OR ITS DERIVATIVES.
 * 
 * IN NO EVENT WILL ACCENTRIX BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR
 * FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES,
 * HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE
 * USE OF OR INABILITY TO USE SOFTWARE, EVEN IF ACCENTRIX HAS BEEN ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGES.
 */
package com.lightsaber.trade.as.service.planfirst;

import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.dom4j.Attribute;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.lightsaber.trade.as.dao.category.CategoryLeafDao;
import com.lightsaber.trade.as.dao.category.CategoryNodeDao;
import com.lightsaber.trade.as.dao.planning.CategoryPlanningItemDao;
import com.lightsaber.trade.as.dao.planning.DisciplinePlanningItemDao;
import com.lightsaber.trade.as.dao.planning.Level1PlanningDao;
import com.lightsaber.trade.as.dao.planning.Level2PlanningDao;
import com.lightsaber.trade.as.entity.category.Category;
import com.lightsaber.trade.as.entity.category.CategoryLeaf;
import com.lightsaber.trade.as.entity.category.CategoryNode;
import com.lightsaber.trade.as.entity.category.SourceType;
import com.lightsaber.trade.as.entity.planning.CategoryPlanningItem;
import com.lightsaber.trade.as.entity.planning.DisciplinePlanningItem;
import com.lightsaber.trade.as.entity.planning.Level1Planning;
import com.lightsaber.trade.as.entity.planning.Level2Planning;
import com.lightsaber.trade.as.entity.planning.PlanningStatus;
import com.lightsaber.trade.as.service.category.CategoryNodeService;
import com.lightsaber.trade.as.service.category.CategoryService;
import com.lightsaber.trade.as.service.param.ParamService;
import com.lightsaber.trade.core.common.treegrid.GridDoc;
import com.lightsaber.trade.core.common.util.Calc;
import com.lightsaber.trade.core.common.util.DateUtil;
import com.lightsaber.trade.core.dao.BaseDao;
import com.lightsaber.trade.core.service.BaseServiceImpl;

@Service
@Transactional
public class DisciplinePlanningItemManager extends
        BaseServiceImpl<DisciplinePlanningItem, String> {
    private final static Logger log = LoggerFactory.getLogger(DisciplinePlanningItemManager.class);

    public final static String PLAN = "PLANNED_PLACES";
    public final static String PROC = "PLANNED_PROCESSING";
    public final static String PNEW = "PLANNED_NEW";
    public final static String ENRO = "ENROLMENT";

    @Autowired
    DisciplinePlanningItemDao disciplinePlanningItemDao;
    @Autowired
    Level1PlanningDao level1PlanningDao;
    @Autowired
    CategoryNodeProgrameTempManager categoryNodeProgrameTempManager;
    @Autowired
    CategoryNodeDao categoryNodeDao;
    @Autowired
    CategoryService categoryService;
    @Autowired
    CategoryNodeService categoryNodeService;
    @Autowired
    CategoryPlanningItemDao categoryPlanningItemDao;
    @Autowired
    CategoryLeafDao categoryLeafDao;
    @Autowired
    Level2PlanningDao level2PlanningDao;

    @Autowired
    ParamService paramService;

    @Override
    protected BaseDao<DisciplinePlanningItem, String> getDao() {
        return disciplinePlanningItemDao;
    }

    // Main Control method
    public String generateTreeGrid(final String level1PlanningId, final String categoryNodeId)
            throws Exception {
        StringBuffer gridDoc = new StringBuffer();
        gridDoc.append("<Grid><Cfg id='LevelOnePlanningDiscipline' MainCol='A' ColMoving='0' Dragging='0' Sorting='0' Alternate='0'/>");

        Level1Planning level1Planning = level1PlanningDao.get(level1PlanningId);
        Category category = level1Planning.getCategory();
        CategoryNode categoryNode = categoryNodeDao.get(categoryNodeId);

        // At first view, generate the grid
        long cnt = disciplinePlanningItemDao.countByPlanningAndCategoryNode(level1Planning,
                categoryNode);
        if (cnt <= 0) {
            saveDiscipline(level1Planning, categoryNode);
        }

        // 1. Add columns to Grid
        generateLefCols(gridDoc);

        // 2. Add columns to Grid
        generateCols(gridDoc, category, level1Planning.getStatus());

        // 3. Add body to Grid
        generateBody(gridDoc, level1Planning, categoryNode, category);

        // 4. Add Head to Grid
        generateHead(gridDoc, category);

        // 5. Add Def to Grid
        generateDef(gridDoc, category);

        // 6. Add foot
        generateFoot(gridDoc);

        gridDoc.append("<Toolbar Visible='0'/>");
        gridDoc.append("<Panel Copy='0' Move='0' Select='0' Delete='0'/></Grid>");
        return gridDoc.toString();
    }

    private void generateFoot(final StringBuffer gridDoc) {
        StringBuilder foot = new StringBuilder();

        foot.append("<Foot>");

        foot.append("<I Def='Tree' A='&lt;B&gt;Total&lt;/B&gt;' AType='Html' CanEdit='0' />");

        foot.append("</Foot>");

        log.debug("Foot ={}", foot.toString());
        gridDoc.append(foot.toString());
    }

    private void generateBody(final StringBuffer gridDoc, final Level1Planning level1Planning,
            final CategoryNode categoryNode, final Category category) {
        List<DisciplinePlanningItem> itemList = disciplinePlanningItemDao.findByPlanAndNode(
                level1Planning, categoryNode);

        if (itemList.size() <= 0) {
            return;
        }

        int canEditActualEnrolment = 0;
        if (categoryNode.getSourceType() == SourceType.ManualInput) {
            canEditActualEnrolment = 1;
        }

        StringBuilder body = new StringBuilder();
        DisciplinePlanningItem item = null;
        String discipline = itemList.get(0).getDiscipline();
        String fundingMode = itemList.get(0).getFundingMode();
        Integer planYear = category.getPlanYear();

        body.append("<Body><B>");
        for (int i = 0; i < itemList.size(); i++) {
            item = itemList.get(i);

            // 1. the first one
            if (i == 0) {
                body.append("<I Def='Tree' id='" + discipline + "' A='"
                        + paramService.getDisciplineLabel(discipline) + "' ><I Def='Leaf' id='"
                        + discipline + "_" + fundingMode + "' A='' B='"
                        + paramService.getFundingModeLabel(fundingMode) + "' ");
            }

            // 2. when change row
            if (!discipline.equals(item.getDiscipline())) {
                discipline = item.getDiscipline();
                fundingMode = item.getFundingMode();
                body.append("   ></I></I><I Def='Tree' id='" + discipline + "' A='"
                        + paramService.getDisciplineLabel(discipline) + "' ><I Def='Leaf' id='"
                        + discipline + "_" + fundingMode + "' A='' B='"
                        + paramService.getFundingModeLabel(fundingMode) + "' ");

            } else if (!fundingMode.equals(item.getFundingMode())) {

                fundingMode = item.getFundingMode();
                body.append("   ></I><I Def='Leaf' id='" + discipline + "_" + fundingMode
                        + "' A='' B='" + paramService.getFundingModeLabel(fundingMode) + "' ");
            }

            // 3. add column
            Integer year = item.getPlanYear();
            String prefix = "_" + year + "_" + (year + 1) + "_";

            if (year.compareTo(planYear) > 0) {
                body.append(prefix + PLAN + "='" + item.getPlannedPlaces() + "' ");

            } else if (year.compareTo(planYear) == 0) {
                // Determine whether the plan year

                body.append(prefix + PNEW + "='" + item.getPlannedNew() + "' ");
                body.append(prefix + PROC + "='" + item.getPlannedProgressing() + "' ");
                body.append(prefix + PLAN + "='" + item.getPlannedPlaces() + "' ");
                body.append(prefix + ENRO + "='" + item.getActualEnrolment() + "' " + prefix + ENRO
                        + "CanEdit='" + canEditActualEnrolment + "' ");

            } else if (year.compareTo(planYear - 1) == 0) {
                body.append(prefix + PLAN + "='" + item.getPlannedPlaces() + "' ");
                body.append(prefix + ENRO + "='" + item.getActualEnrolment() + "' " + prefix + ENRO
                        + "CanEdit='" + canEditActualEnrolment + "' ");

            } else if (year.compareTo(planYear - 1) < 0) {
                body.append(prefix + PLAN + "='" + item.getPlannedPlaces() + "' ");
                body.append(prefix + ENRO + "='" + item.getActualEnrolment() + "' " + prefix + ENRO
                        + "CanEdit='" + canEditActualEnrolment + "' ");
            }

            // 4. the last one
            if (i == (itemList.size() - 1)) {
                body.append("></I></I>");
            }
        }

        body.append("</B></Body>");

        String b = body.toString();
        log.debug("Body ={}", b);
        gridDoc.append(b);
    }

    private void generateHead(final StringBuffer gridDoc, final Category category) {

        StringBuilder header1 = new StringBuilder();
        StringBuilder header2 = new StringBuilder();

        Integer level1Start = category.getLevel1Start();
        Integer level1End = category.getLevel1End();
        Integer planYear = category.getPlanYear();

        header1.append("<Header Spanned='1' ASpan='2' A='Planning Exercise : " + planYear + "/"
                + DateUtil.formatYear(planYear + 1, "yy") + "' AType='Html' Aalign='Center' ");

        header2.append("<Header id='Header' A='Discipline' AAlign='Left' AType='Html' B='Funding&lt;br/&gt;Mode' BAlign='Center' BType='Html' ");

        for (Integer i = level1Start; i <= level1End; i++) {
            String prefix = "_" + i + "_" + (i + 1) + "_";
            String headName = i + "-" + (i + 1);

            if (i.compareTo(planYear) > 0) {
                header1.append(prefix + PLAN + "Span='1' " + prefix + PLAN + "='" + headName + "' "
                        + prefix + PLAN + "Align='Center' " + prefix + PLAN + "Type='Html' ");

                header2.append(prefix + PLAN + "='Planned&lt;br/&gt;Places' " + prefix + PLAN
                        + "Align='Center' " + prefix + PLAN + "Type='Html' ");

            } else if (i.compareTo(planYear) == 0) {// Determine whether the
                                                    // plan year
                header1.append(prefix + PNEW + "Span='4' " + prefix + PNEW + "='" + headName + "' "
                        + prefix + PNEW + "Align='Center' " + prefix + PNEW + "Type='Html' ");

                header2.append(prefix + PNEW + "='Planned&lt;br/&gt;New' " + prefix + PNEW
                        + "Align='Center' " + prefix + PNEW + "Type='Html' ");
                header2.append(prefix + PROC + "='Planned&lt;br/&gt;Progressing' " + prefix + PROC
                        + "Align='Center' " + prefix + PROC + "Type='Html' ");
                header2.append(prefix + PLAN + "='Total Planned&lt;br/&gt;Places' " + prefix + PLAN
                        + "Align='Center' " + prefix + PLAN + "Type='Html' ");
                header2.append(prefix + ENRO + "='Enrolment' " + prefix + ENRO + "Align='Center' "
                        + prefix + ENRO + "Type='Html' ");

            } else if (i.compareTo(planYear - 1) == 0) {

                header1.append(prefix + PLAN + "Span='2' " + prefix + PLAN + "='" + headName + "' "
                        + prefix + PLAN + "Align='Center' " + prefix + PLAN + "Type='Html' ");

                header2.append(prefix + PLAN + "='Planned&lt;br/&gt;Places' " + prefix + PLAN
                        + "Align='Center' " + prefix + PLAN + "Type='Html' ");
                header2.append(prefix + ENRO + "='Enrolment' " + prefix + ENRO + "Align='Center' "
                        + prefix + ENRO + "Type='Html' ");

            } else if (i.compareTo(planYear - 1) < 0) {

                header1.append(prefix + PLAN + "Span='2' " + prefix + PLAN + "='" + headName + "' "
                        + prefix + PLAN + "Align='Center' " + prefix + PLAN + "Type='Html' ");

                header2.append(prefix + PLAN + "='Planned&lt;br/&gt;Places' " + prefix + PLAN
                        + "Align='Center' " + prefix + PLAN + "Type='Html' ");
                header2.append(prefix + ENRO + "='Enrolment' " + prefix + ENRO + "Align='Center' "
                        + prefix + ENRO + "Type='Html' ");

            }

        }

        header1.append("/>");
        header2.append("/>");

        String head = "<Head>" + header1.toString() + header2.toString() + "</Head>";
        log.debug("Head ={}", head);
        gridDoc.append(head);

    }

    private void generateDef(final StringBuffer gridDoc, final Category category) {

        StringBuilder treeDef = new StringBuilder();
        StringBuilder leafDef = new StringBuilder();

        Integer level1Start = category.getLevel1Start();
        Integer level1End = category.getLevel1End();
        Integer planYear = category.getPlanYear();

        treeDef.append(" <D Name='Tree' Calculated='1'  ");

        leafDef.append(" <D Name='Leaf' Calculated='1'  ");

        for (Integer i = level1Start; i <= level1End; i++) {
            String prefix = "_" + i + "_" + (i + 1) + "_";

            if (i.compareTo(planYear) > 0) {
                treeDef.append(prefix + PLAN + "Formula='sum()' ");

            } else if (i.compareTo(planYear) == 0) {// Determine whether the
                treeDef.append(prefix + PNEW + "Formula='sum()' ");
                treeDef.append(prefix + PROC + "Formula='sum()' ");
                treeDef.append(prefix + PLAN + "Formula='sum()' ");
                treeDef.append(prefix + ENRO + "Formula='sum()' ");

                leafDef.append(prefix + PNEW + "Formula='" + prefix + PLAN + "-" + prefix + PROC
                        + "' ");
                // plan year

            } else if (i.compareTo(planYear - 1) == 0) {
                treeDef.append(prefix + PLAN + "Formula='sum()' ");
                treeDef.append(prefix + ENRO + "Formula='sum()' ");

            } else if (i.compareTo(planYear - 1) < 0) {
                treeDef.append(prefix + PLAN + "Formula='sum()' ");
                treeDef.append(prefix + ENRO + "Formula='sum()' ");
            }

        }

        treeDef.append("/>");

        leafDef.append("/>");

        String defXML = "<Def>" + treeDef.toString() + leafDef.toString() + "</Def>";
        log.debug("Def ={}", defXML);
        gridDoc.append(defXML);

    }

    private void generateLefCols(final StringBuffer gridDoc) {
        String lefCols = "<LeftCols><C Name='A' Width='120' CanEdit='0' />"
                + "<C Name='B' Width='60' CanEdit='0' Align='Center' /></LeftCols>";

        gridDoc.append(lefCols);
    }

    private void generateCols(final StringBuffer gridDoc, final Category category,
            final PlanningStatus planningStatus) {
        Integer planYear = category.getPlanYear();
        Integer level1Start = category.getLevel1Start();
        Integer level1End = category.getLevel1End();

        StringBuffer colsSb = new StringBuffer();

        colsSb.append("<Cols>");

        // planning status must control edit
        String canEdit = "0";
        if (planningStatus.getCanEditLevel1()) {
            canEdit = "1";
        }

        for (Integer i = level1Start; i <= level1End; i++) {
            String prefix = "_" + i + "_" + (i + 1) + "_";

            if (i.compareTo(planYear) > 0) {

                colsSb.append(" <C Name='" + prefix + PLAN
                        + "' Width='90' Type='Float' Align='Center' CanEdit='" + canEdit + "' />");

            } else if (i.compareTo(planYear) == 0) {// Determine whether the
                                                    // plan year

                colsSb.append(" <C Name='" + prefix + PNEW
                        + "' Width='90' Type='Float' Align='Center' CanEdit='0' />");
                colsSb.append(" <C Name='" + prefix + PROC
                        + "' Width='90' Type='Float' Align='Center' CanEdit='" + canEdit + "' />");
                colsSb.append(" <C Name='" + prefix + PLAN
                        + "' Width='90' Type='Float' Align='Center' CanEdit='" + canEdit + "' />");
                colsSb.append(" <C Name='" + prefix + ENRO
                        + "' Width='90' Type='Float' Align='Center' CanEdit='" + canEdit + "' />");

            } else if (i.compareTo(planYear - 1) == 0) {

                colsSb.append(" <C Name='" + prefix + PLAN
                        + "' Width='90' Type='Float' Align='Center' CanEdit='" + canEdit + "' />");
                colsSb.append(" <C Name='" + prefix + ENRO
                        + "' Width='90' Type='Float' Align='Center' CanEdit='" + canEdit + "' />");

            } else if (i.compareTo(planYear - 1) < 0) {

                colsSb.append(" <C Name='" + prefix + PLAN
                        + "' Width='90' Type='Float' Align='Center' CanEdit='" + canEdit + "' />");
                colsSb.append(" <C Name='" + prefix + ENRO
                        + "' Width='90' Type='Float' Align='Center' CanEdit='" + canEdit + "' />");
            }

        }

        colsSb.append("</Cols>");

        String cols = colsSb.toString();
        log.debug("Def ={}", cols);
        gridDoc.append(cols);

    }

    /**
     * Generate all the discipline item of the planning and category node
     * 
     * @author LiuJian
     * @param level1Planning
     * @param categoryNode
     * @throws Exception
     */
    private void saveDiscipline(final Level1Planning level1Planning, final CategoryNode categoryNode)
            throws Exception {

        try {
            // 1. Search all programs of the node and compute the planned
            // processing of the planYear
            Level2Planning level2Planning = level2PlanningDao.findByPlanningExercise(level1Planning
                    .getPlanningExercise());

            categoryNodeProgrameTempManager.saveProgrameAsMe(level1Planning, level2Planning,
                    categoryNode, categoryNode);

            // 2. statistic
            List<Object[]> result = categoryNodeProgrameTempManager.group(level1Planning,
                    categoryNode);

            Category category = level1Planning.getCategory();
            Integer planYear = category.getPlanYear();
            Integer level1Start = category.getLevel1Start();
            Integer level1End = category.getLevel1End();

            DisciplinePlanningItem item = null;
            for (Object[] objects : result) {
                String discipline = (String) objects[0];
                String fundingMode = (String) objects[1];

                for (Integer i = level1Start; i <= level1End; i++) {
                    item = new DisciplinePlanningItem();

                    // 1-5 property
                    item.setLevel1Planning(level1Planning);
                    item.setCategoryNode(categoryNode);
                    item.setDiscipline(discipline);
                    item.setFundingMode(fundingMode);
                    item.setPlanYear(i);

                    if (i.compareTo(planYear) > 0) {
                        item.setPlannedPlaces(0);

                    } else if (i.compareTo(planYear) == 0) {
                        item.setPlannedProgressing((Long) objects[2]);// sum
                        item.setPlannedPlaces(0);
                        item.setPlannedNew(0);
                        item.setActualEnrolment(0);// TODO

                    } else if (i.compareTo(planYear - 1) == 0) {
                        long plannedPlaces = getPlannedPlacesInYear(i, categoryNode, discipline,
                                fundingMode);

                        item.setPlannedPlaces(plannedPlaces);
                        item.setActualEnrolment(0);// TODO

                    } else if (i.compareTo(planYear - 1) < 0) {

                        long plannedPlaces = getPlannedPlacesInYear(i, categoryNode, discipline,
                                fundingMode);

                        item.setPlannedPlaces(plannedPlaces);
                        item.setActualEnrolment(0);// TODO
                    }

                    disciplinePlanningItemDao.save(item);
                }
            }

            // 3. deep statistic
            PlacesStore thisYearTotalStrore = new PlacesStore();
            sumPlanPlacesInPlanItemByNodeAndPlaningInYear(thisYearTotalStrore, level1Planning,
                    categoryNode, planYear);

            Long thisYearTotal = thisYearTotalStrore.getPlaces();
            // no places
            if (thisYearTotal == 0) {
                return;
            }

            long lastYearTotal = disciplinePlanningItemDao.totalPlanedPlacesByPlanAndNodeAndYear(
                    level1Planning, categoryNode, planYear - 1);

            if (lastYearTotal == 0) {// share the places
                long thisYearItemCnt = disciplinePlanningItemDao.countByPlanAndNodeAndYear(
                        level1Planning, categoryNode, planYear);

                if (thisYearItemCnt == 0) {
                    return;
                }

                String share = Calc.div2Integer(thisYearTotal, thisYearItemCnt);

                List<DisciplinePlanningItem> disciplingItems = disciplinePlanningItemDao
                        .findByPlanAndNodeAndYear(level1Planning, categoryNode, planYear);

                for (DisciplinePlanningItem disciplingItem : disciplingItems) {
                    disciplingItem.setPlannedPlaces(Integer.valueOf(share));
                }

            } else {

                List<DisciplinePlanningItem> disciplingItems = disciplinePlanningItemDao
                        .findByPlanAndNodeAndYear(level1Planning, categoryNode, planYear);

                DisciplinePlanningItem LastYearDisciplingItem = null;

                for (DisciplinePlanningItem disciplingItem : disciplingItems) {

                    LastYearDisciplingItem = disciplinePlanningItemDao.findUnique(level1Planning,
                            categoryNode, planYear - 1, disciplingItem.getDiscipline(),
                            disciplingItem.getFundingMode());

                    disciplingItem
                            .setPlannedPlaces(Calc.divMul2Integer(
                                    LastYearDisciplingItem.getPlannedPlaces(), lastYearTotal,
                                    thisYearTotal));
                }
            }

            // calculate the planned new
            List<DisciplinePlanningItem> disciplingItems = disciplinePlanningItemDao
                    .findByPlanAndNodeAndYear(level1Planning, categoryNode, planYear);
            for (DisciplinePlanningItem ditem : disciplingItems) {
                ditem.setPlannedNew(ditem.getPlannedPlaces() - ditem.getPlannedProgressing());
            }

        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {

            // finally, clear the temp table
            categoryNodeProgrameTempManager.clear(level1Planning, categoryNode);
        }

    }

    private static class PlacesStore {
        private Long places = new Long(0);

        public Long getPlaces() {
            return places;
        }

        public void setPlaces(final Long places) {
            this.places = places;
        }

    }

    private void sumPlanPlacesInPlanItemByNodeAndPlaningInYear(final PlacesStore totalPlanedPlaces,
            final Level1Planning level1Planning, final CategoryNode categoryNode,
            final Integer planYear) {

        if (categoryNode.getIsLeafNode() == 1) {
            CategoryLeaf leaf = categoryLeafDao.get(categoryNode.getId());
            CategoryPlanningItem cpi = categoryPlanningItemDao.findByPlanAndLeafAndYear(
                    level1Planning, leaf, planYear);

            totalPlanedPlaces.setPlaces(totalPlanedPlaces.getPlaces() + cpi.getPlannedPlaces());

        } else {
            List<CategoryNode> children = categoryNode.getChildren();
            for (CategoryNode child : children) {
                sumPlanPlacesInPlanItemByNodeAndPlaningInYear(totalPlanedPlaces, level1Planning,
                        child, planYear);
            }
        }
    }

    private long getPlannedPlacesInYear(final Integer planYear, final CategoryNode categoryNode,
            final String discipline, final String fundingMode) throws Exception {
        // 1. find the history category
        Category oldMasterTree = categoryService.findMasterTreeByPlanYear(planYear);

        if (oldMasterTree == null) {
            return 0;
        }

        // 2. find the history level1Planning owned by old category
        Level1Planning oldLevel1Planning = level1PlanningDao
                .findEndorsedByMasterTree(oldMasterTree);

        if (oldLevel1Planning == null) {
            return 0;
        }

        // 3. find the history category node
        List<String> path = categoryNodeService.getTreePathOfNode(categoryNode);
        CategoryNode oldCategoryNode = categoryNodeService.getNodeByPath(path, oldMasterTree);

        if (oldCategoryNode == null) {
            return 0;
        }

        DisciplinePlanningItem oldItem = disciplinePlanningItemDao.findUnique(oldLevel1Planning,
                oldCategoryNode, planYear, discipline, fundingMode);

        if (oldItem == null) {
            return 0;
        }

        return oldItem.getPlannedPlaces();
    }

    public void save(final String treeGridData, final String level1PlanningId,
            final String categoryNodeId) {
        log.info("XML = {}", treeGridData);
        Iterator<Element> ite;

        try {
            ite = GridDoc.parseChanges(treeGridData);
        } catch (DocumentException e) {
            log.error(e.getMessage(), e);
            return;
        }

        while (ite.hasNext()) {
            Element elemI = ite.next();

            if (!StringUtils.equals("1", elemI.attributeValue("Changed"))) {
                continue;
            }

            String id = elemI.attributeValue("id");
            log.info("id: {}", id);
            if (StringUtils.isBlank(id)) {
                continue;
            }

            String[] ids = id.split("_");
            String discipline = ids[0];
            String fundingMode = ids[1];

            Iterator<Attribute> attrs = elemI.attributeIterator();

            while (attrs.hasNext()) {
                Attribute attr = attrs.next();

                // 1. find column name，example _2011_2012_PLANNED_PLACES,
                // _2011_2012_PLANNED_NEW,
                String cellName = attr.getName();
                if (!cellName.startsWith("_")) {
                    continue;
                }

                // 2. find<I id="" Changed="1" _2011_2012_PLANNED_PLACES="25"/>
                // the value "25" of attribute _2011_2012_PLANNED_PLACES
                String cellvalue = attr.getValue();
                Integer.parseInt(cellvalue);

                // 2. Get "2014" of attribute _2014_2015_3 in <I id=""
                // Changed="1" _2014_2015_3="25"/>
                String planYear = cellName.substring(1, cellName.indexOf("_", 1));

                DisciplinePlanningItem item = disciplinePlanningItemDao.findByAllRestriction(
                        level1PlanningId, categoryNodeId, Integer.valueOf(planYear), discipline,
                        fundingMode);

                if (cellName.endsWith(PLAN)) {
                    item.setPlannedPlaces(Long.valueOf(cellvalue));

                } else if (cellName.endsWith(PROC)) {
                    item.setPlannedProgressing(Long.valueOf(cellvalue));

                } else if (cellName.endsWith(PNEW)) {
                    item.setPlannedNew(Long.valueOf(cellvalue));

                } else {
                    item.setActualEnrolment(Long.valueOf(cellvalue));
                }

                disciplinePlanningItemDao.save(item);
            }

        }
    }
}
